package io.gitee.macxiang.sql.wapper;

import java.util.ArrayList;
import java.util.List;
import java.util.function.Function;
import java.util.regex.Pattern;

import io.gitee.macxiang.entity.ExecEntity;
import io.gitee.macxiang.entity.ExecNumber;
import io.gitee.macxiang.entity.Page;
import io.gitee.macxiang.utils.Datas;
import io.gitee.macxiang.utils.utils;

/** where语句的构造器
 * @param <E> 实体类
 * @param <W> where语句的构造器
 * @param <S> set语句的构造器
 */
public class WhereResult<E, W extends BaseWhere, S extends BaseSet> {
  final public BaseService<E, W, S> service;
  public String select;
  public String join;
  public String where;
  public String groupBy;
  public String having;
  public String orderBy;
  public String limit = "10";

  public WhereResult(String where, BaseService<E, W, S> service) {
    this.service = service;
    this.where = where;
    select = service.selectField;
  }

  /**获取sql语句
   * @param count 是否为计算语句查询的数量
   * @return      SQL语句
   */
  public String selectSql(boolean count) {
    String ret = count
        ? String.format("SELECT COUNT(*) FROM %s", service.sqlTableName)
        : String.format("SELECT %s FROM %s", (Datas.isEmpty(select) ? "*" : select), service.sqlTableName);
    if (!Datas.isEmpty(join)) {
      if (!Pattern.compile("\\bjoin\\s+", Pattern.CASE_INSENSITIVE).matcher(join).find()) {
        ret += " JOIN";
      }
      ret += " " + join.trim();
    }
    if (!Datas.isEmpty(where)) {
      ret += " WHERE " + where;
    }
    if (!Datas.isEmpty(groupBy)) {
      ret += " GROUP BY " + groupBy;
    }
    if (!Datas.isEmpty(having)) {
      ret += " HAVING " + having;
    }
    if (!Datas.isEmpty(orderBy)) {
      ret += " ORDER BY " + orderBy;
    }
    if (!count && !Datas.isEmpty(limit)) {
      ret += " LIMIT " + limit;
    }
    return ret;
  }

  /** 条件计数
   * @return          {@link ExecNumber} 方便查看sql
   */
  public ExecNumber count() {
    // return service.databaseService.count(selectSql(true));
    return new ExecNumber(selectSql(true), p -> service.databaseService.count(p));
  }

  /**按字段进行分组
   * @param sqlName 数据库中的字段名
   * @return        {@link WhereResult}自身,方便链式操作
   */
  public WhereResult<E, W, S> groupBy(String sqlName) {
    if (groupBy == null) {
      groupBy = "";
    }
    groupBy = groupBy.trim();
    groupBy += (groupBy.isEmpty() ? "" : ", ") + sqlName;
    return this;
  }

  /**按字段进行排序
   * @param sqlName 数据库中的字段名
   * @param isASC   是否升序
   * @return        {@link WhereResult}自身,方便链式操作
   */
  public WhereResult<E, W, S> orderBy(String sqlName, boolean isASC) {
    if (orderBy == null) {
      orderBy = "";
    }
    orderBy = orderBy.trim();
    orderBy += (orderBy.isEmpty() ? "" : ", ") + sqlName + (isASC ? " ASC" : " DESC");
    return this;
  }

  /**修改限制语句
   * @param currentPage 当前页
   * @param size        数据数量
   * @return            {@link WhereResult}自身,方便链式操作
   */
  public WhereResult<E, W, S> limit(int currentPage, int size) {
    if (currentPage < 1) {
      currentPage = 1;
    }
    if (size < 1) {
      size = 10;
    }
    if (size > service.sqlConfig.maxcount) {
      size = service.sqlConfig.maxcount;
    }

    limit = String.format("%d,%d", (currentPage - 1) * size, size);
    return this;
  }

  /**修改限制语句
   * @param size    数据数量
   * @return        {@link WhereResult}自身,方便链式操作
   */
  public WhereResult<E, W, S> limit(int size) {
    return limit(0, size);
  }

  /**分页查询
   * @param current 当前页;最小为1
   * @param size    页大小;最小为1,最大值为配置mac.sql.maxcount,默认100
   * @return        分页数据
   */
  public Page<E> page(int current, int size) {
    if (current < 1) {
      current = 1;
    }
    if (size < 1) {
      size = 10;
    }
    if (size > service.sqlConfig.maxcount) {
      size = service.sqlConfig.maxcount;
    }
    limit((current - 1) * size, size);
    Page<E> ret = new Page<>(
        service.databaseService.count(selectSql(true)),
        current, size);
    ret.data = service.databaseService.selectList(selectSql(false), service.ce, service.fs);
    return ret;
  }

  private String getUpdateSelectSql() {
    return String.format("SELECT %s FROM %s WHERE %s", service.primary.sqlName, service.sqlTableName, where);
  }

  /**执行修改
   * @param callback  回调函数,可在回调函数中设置要修改的字段
   * @return          {@link ExecNumber} 方便查看sql
   */
  public ExecNumber update(java.util.function.Consumer<S> callback) {
    try {
      S s = service.cs.getDeclaredConstructor().newInstance();
      if (callback != null) {
        callback.accept(s);
      }
      String ss = s.getSql(service.sqlTableName), id = service.primary.sqlName;
      if (!Datas.isEmpty(ss) && !Datas.isEmpty(where)) {
        String sql = String.format("UPDATE %s SET %s WHERE %s in (SELECT %s FROM (%s) AS subquery)",
            service.sqlTableName, ss, id, id, selectSql(false).replace(select, id));
        return new ExecNumber(sql, p -> service.databaseService.save(p));
      }
    } catch (Exception e) {
      System.err.println("构造update错误");
      utils.out(e);
    }
    return new ExecNumber(null, e -> 0);
  }

  /**以主键为准,执行修改
   * @param entity    修改的数据; 主键必须存在;
   * @return          {@link ExecNumber} 方便查看sql
   */
  public ExecNumber update(E entity) {
    if (entity != null) {
      List<String> s = new ArrayList<>();
      service.fs.forEach(e -> {
        if (e != service.primary) {
          String v = e.getValue(entity, false);
          if (v != null) {
            s.add(String.format("%s=%s", e.sqlName, v));
          }
        }
      });
      String ss = String.join(", ", s), id = service.primary.sqlName;
      String sql = String.format("UPDATE %s SET %s WHERE %s in (SELECT %s FROM (%s) AS subquery)",
          service.sqlTableName, ss, id, id, getUpdateSelectSql());
      return new ExecNumber(sql, p -> service.databaseService.save(p));
    }
    return new ExecNumber(null, e -> 0);
  }

  /**删除数据;删除时,具有limit限制,默认是10,如果要修改数量,请在执行前手动修改属性limit
   * @return  {@link ExecNumber} 方便查看sql
   */
  public ExecNumber delete() {
    String id = service.primary.sqlName,
        sql = selectSql(false).replace(select, id);
    return service.deleteByWhere(String.format("%s IN (%s)", id, sql));
  }

  /**执行select语句查询
   * @return  {@link ExecEntity} 方便查看sql
   */
  public ExecEntity<List<E>> select() {
    return new ExecEntity<>(selectSql(false), sql -> service.databaseService.selectList(sql, service.ce, service.fs));
  }

  /**修改select查询字段
   * @param select  新的字段
   * @return        {@link WhereResult}自身,方便链式操作
   */
  public WhereResult<E, W, S> select(String select) {
    this.select = select;
    return this;
  }

  /**修改select查询字段
   * @param selectCallback  设置select字段的回调函数; 参数为原来的select字段,返回新的select字段
   * @return        {@link WhereResult}自身,方便链式操作
   */
  public WhereResult<E, W, S> select(Function<String, String> selectCallback) {
    try {
      this.select = selectCallback.apply(select);
    } catch (Exception e) {
    }
    return this;
  }

  /** 设置join
   * @param join     join语句
   * @return        {@link WhereResult}自身,方便链式操作
   */
  public WhereResult<E, W, S> join(String join) {
    this.join = join;
    return this;
  }

  /** 添加join
   * @param joinCallback  join语句的回调函数; 函数参数为原来的join语句,返回新的join语句
   * @return        {@link WhereResult}自身,方便链式操作
   */
  public WhereResult<E, W, S> join(Function<String, String> joinCallback) {
    try {
      this.join = joinCallback.apply(join);
    } catch (Exception e) {
    }
    return this;
  }

  /**执行select语句查询
   * @return  {@link ExecEntity} 方便查看sql
   */
  public ExecEntity<E> selectOne() {
    return new ExecEntity<>(selectSql(false), sql -> service.databaseService.selectOne(sql, service.ce, service.fs));
  }

  /**继续增加where条件
  * @param callback 回调函数
  * @return         {@link WhereResult}自身,方便链式操作
  */
  public WhereResult<E, W, S> where(java.util.function.Consumer<W> callback) {
    return where(callback, false);
  }

  /**继续增加where条件
  * @param callback 回调函数
  * @param clear    是否清空之前的where条件
  * @return         {@link WhereResult}自身,方便链式操作
  */
  public WhereResult<E, W, S> where(java.util.function.Consumer<W> callback, boolean clear) {
    try {
      W w = service.cw.getDeclaredConstructor().newInstance();
      if (callback != null) {
        callback.accept(w);
      }
      String s = w.getSql(service.sqlTableName, false);
      if (clear || utils.isEmpty(where)) {
        where = s.replaceAll("^\\s*\\b(AND|OR)\\b\\s*", "");
      } else {
        where += s;
      }
    } catch (Exception e) {
    }
    return this;
  }
}
